# -*- coding: utf-8 -*-

from new_test_framework.utils import tdLog, tdSql, tdDnodes
from new_test_framework.utils.sqlset import TDSetSql
from decimal import Decimal
import os
import time
import shutil

class TestCase:
    path_parts = os.getcwd().split(os.sep)
    try:
        tdinternal_index = path_parts.index("TDinternal")
    except ValueError:
        raise ValueError("The specified directory 'TDinternal' was not found in the path.")
    TDinternal = os.sep.join(path_parts[:tdinternal_index + 1])
    dnode1Path = os.path.join(TDinternal, "sim", "dnode1")
    configFile = os.path.join(dnode1Path, "cfg", "taos.cfg")
    hostPath = os.path.join(dnode1Path, "multi")
    hostPrimary = os.path.join(hostPath, "taos01")
    mountPath = os.path.join(dnode1Path, "mnt")
    mountPrimary = os.path.join(mountPath, "taos01")
    clientCfgDict = {'debugFlag': 135}
    updatecfgDict = {
        "debugFlag"        : 135,
        "forceReadConfig"  : 1,
        "dataDir"          : [  f"%s%staos00 0 0" % (hostPath, os.sep),
                                f"%s%staos01 0 1" % (hostPath, os.sep),
                                f"%s%staos02 0 0" % (hostPath, os.sep),
                                f"%s%staos10 1 0" % (hostPath, os.sep),
                                f"%s%staos11 1 0" % (hostPath, os.sep),
                                f"%s%staos12 1 0" % (hostPath, os.sep)],
        'clientCfg'        : clientCfgDict
    }

    def setup_cls(cls):
        tdLog.debug("start to execute %s" % __file__)
        cls.setsql = TDSetSql()

    def s0_reset_test_env(self):
        tdLog.info("reset test environment")
        self.s1_create_db_table()
        self.s2_create_rsma()

    def s1_create_db_table(self):
        tdSql.execute("alter all dnodes 'queryTrimIntervalSec 1'")
        tdSql.execute("drop database if exists d0")
        tdSql.execute("create database if not exists d0 replica 1 keep 36500d")
        tdSql.execute("drop database if exists d1")
        tdSql.execute("create database if not exists d1 replica 1 keep 36500d")
        tdSql.execute("use d0")
        tdSql.execute("create stable if not exists stb0 (ts timestamp, c0 int, c1 bigint, c2 float, c3 double, c4 bool, c5 varchar(10), c6 nchar(10)) tags(t0 int)")
        tdSql.execute("create stable if not exists stb1 (ts timestamp, c00 varchar(10), c0 int, c1 bigint, c2 float, c3 double, c4 bool, c5 varchar(10), c6 nchar(10)) tags(t0 int)")
        tdSql.execute("create table if not exists ntb0 (ts timestamp, c0 int, c1 bigint, c2 float, c3 double, c4 bool, c5 varchar(10), c6 nchar(10))")
        tdSql.execute("create table if not exists ctb0 using stb0 tags(0)")
        tdSql.execute("create table if not exists ctb1 using stb0 tags(1)")
        tdSql.execute("create table if not exists ctb11 using stb1 tags(11)")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:00:01.001',1,1,1,1,true, '1','1')")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:00:02.002',2,2,2,2,false, '2','2')")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:00:03.003',3,3,3,3,true, '3','3')")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:01:00.001',4,5,4,7,false, '44','4')")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:01:00.002',4,4,6,4,false, '4','444')")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:02:00.002',5,5,5,5,true, '5','5')")
        tdSql.execute("insert  into ctb1 values('2024-10-01 08:00:01.001',111,11,1,1,true, '91','1')")
        tdSql.execute("insert  into ctb1 values('2024-10-01 08:00:02.002',22,2,2,2,false, '23','2')")
        tdSql.execute("insert  into ctb1 values('2024-10-01 08:00:03.003',33,333,3,3,true, '3','33')")
        tdSql.execute("insert  into ctb1 values('2024-10-01 08:01:00.001',14,4,4,4,false, '40','4')")
        tdSql.execute("insert  into ctb1 values('2024-10-01 08:02:00.002',5,555,5,5,true, '5','5')")
        tdSql.execute("insert  into ctb11 values('2024-10-01 08:00:01.001','111',111,11,1,1,true, '91','1')")
        tdSql.execute("insert  into ctb11 values('2024-10-01 08:00:02.002','22',22,2,2,2,false, '23','2')")
        tdSql.execute("insert  into ctb11 values('2024-10-01 08:00:03.003','33',33,333,3,3,true, '3','33')")
        tdSql.execute("insert  into ctb11 values('2024-10-01 08:01:00.001','14',14,4,4,4,false, '40','4')")
        tdSql.execute("insert  into ctb11 values('2024-10-01 08:02:00.002','55',5,555,5,5,true, '5','5')")
        tdSql.execute("flush database d0")
        tdSql.execute("select * from stb0")

    def s2_create_rsma(self):
        tdSql.execute("create rsma rsma1 on d0.stb0 function(min(c0), max(c1), avg(c2), sum(c3),last(c5)) interval(1m,5m)")
        
        tdSql.error("alter rsma rsma1 function(min(c0), max(c1), avg(c2), sum(c3),first(c4),last(c5))")
        tdSql.error("alter rsma rsma1_ function(min(c0))", expectErrInfo=f"Rsma not exist", fullMatched=False)
        tdSql.error("alter rsma rsma1 function(max(c0))", expectErrInfo=f"Rsma func already specified for column: c0", fullMatched=False)

        tdSql.execute("alter rsma rsma1 function(first(c4))")

        tdSql.error("create rsma rsma1 on d0.stb0 function(min(c0)) interval(1m,5m)", expectErrInfo=f"Rsma already exists", fullMatched=False)
        tdSql.error("create rsma rsma2 on d0.ntb0 interval(1m)", expectErrInfo=f"Rsma must be created on super table", fullMatched=False)
        tdSql.error("create rsma rsma3 on d0.ctb0 interval(1m)", expectErrInfo=f"Rsma must be created on super table", fullMatched=False)
        tdSql.error("create rsma rsma4 on information_schema.ins_users interval(1m)", expectErrInfo=f"Cannot create rsma on system table: `information_schema`.`ins_users`", fullMatched=False)
        tdSql.error("create rsma rsma6 on d0.stb0 interval(1m)", expectErrInfo=f"Rsma already exists in the table", fullMatched=False)
        tdSql.error("create rsma rsma6 on d0.stb0 interval(0m,0m)", expectErrInfo=f"At least one interval value for rsma should be greater than 0", fullMatched=False)
        tdSql.error("create rsma rsma6 on d0.stb0 interval(0m)", expectErrInfo=f"At least one interval value for rsma should be greater than 0", fullMatched=False)
        tdSql.error("create rsma rsma6 on d0.stb0 interval(0)", expectErrInfo=f"Invalid interval unit for rsma:", fullMatched=False)
        tdSql.error("create rsma rsma6 on d0.stb0 interval(-1m)", expectErrInfo=f"Invalid interval value for rsma: -60000, valid range [0, 864000000]", fullMatched=False)
        tdSql.error("create rsma rsma6 on d0.stb0 interval(11d)", expectErrInfo=f"Invalid interval value for rsma: 950400000, valid range [0, 864000000]", fullMatched=False)
        tdSql.error("create rsma rsma6 on d0.stb0 interval(864000001a)", expectErrInfo=f"Invalid interval value for rsma: 864000001, valid range [0, 864000000]", fullMatched=False)

        tdSql.execute("create rsma rsma7 on d0.stb1 function(min(c0), max(c1), avg(c2), sum(c3),first(c4),last(c5),first(c6)) interval(1m,5m)")

        tdSql.error("create rsma rsma8 on d0.stb0 function(min(c0+1), max(c1), avg(c2), sum(c3),first(c4),last(c5)) interval(1m,5m)", expectErrInfo=f"Invalid func param for rsma, only one non-primary key column allowed: min", fullMatched=False)
        tdSql.error("create rsma rsma9 on d0.stb0 function(min(c100), max(c1), avg(c2), sum(c3),first(c4),last(c5)) interval(1m,5m)", expectErrInfo=f"Invalid func param for rsma since column not exist: min(c100)", fullMatched=False)
        tdSql.error("create rsma rsma10 on d0.stb0 function(count(c100), max(c1), avg(c2), sum(c3),first(c4),last(c5)) interval(1m,5m)", expectErrInfo=f"Invalid func param for rsma since column not exist: count(c100)", fullMatched=False)
        tdSql.error("create rsma rsma11 on d0.stb0 function(min(c0), max(c1), avg(c2), sum(c3),first(c4),last(c0)) interval(1m,5m)", expectErrInfo=f"Duplicated column not allowed for rsma: c0", fullMatched=False)
        tdSql.error("create rsma rsma12 on d0.stb0 function(min(c0), max(c1), avg(c2), sum(c3),first(c4),last(c0)) interval(1m,1m)", expectErrInfo=f"Second interval value for rsma should be greater than first interval: 60000,60000", fullMatched=False)
        tdSql.error("create rsma rsma13 on d0.stb0 function(min(c0), max(c1), avg(c2), sum(c3),first(c4),last(c0)) interval(1m,1a)", expectErrInfo=f"Second interval value for rsma should be greater than first interval: 60000,1", fullMatched=False)
        tdSql.error("create rsma rsma14 on d0.stb0 function(min(c0), max(c1), avg(c2), sum(c3),first(c4),last(c0)) interval(2m,3m)", expectErrInfo=f"Second interval value for rsma should be a multiple of first interval: 120000,180000", fullMatched=False)
        tdSql.error("create rsma rsma15 on d0.stb0 function(min(c4)) interval(1m,5m)", expectErrInfo=f"Invalid function para type: min(c4)", fullMatched=False)
        tdSql.error("create rsma rsma16 on d0.stb0 function(max(c4)) interval(1m,5m)", expectErrInfo=f"Invalid function para type: max(c4)", fullMatched=False)
        tdSql.error("create rsma rsma17 on d0.stb0 function(avg(c4)) interval(1m,5m)", expectErrInfo=f"Invalid function para type: avg(c4)", fullMatched=False)
        tdSql.error("create rsma rsma18 on d0.stb0 function(avg(c5)) interval(1m,5m)", expectErrInfo=f"Invalid function para type: avg(c5)", fullMatched=False)
        tdSql.error("create rsma rsma19 on d0.stb0 function(avg(c6)) interval(1m,5m)", expectErrInfo=f"Invalid function para type: avg(c6)", fullMatched=False)
        tdSql.error("create rsma rsma20 on d0.stb0 function(sum(c4)) interval(1m,5m)", expectErrInfo=f"Invalid function para type: sum(c4)", fullMatched=False)
        tdSql.error("create rsma rsma21 on d0.stb0 function(sum(c5)) interval(1m,5m)", expectErrInfo=f"Invalid function para type: sum(c5)", fullMatched=False)
        tdSql.error("create rsma rsma22 on d0.stb0 function(sum(c6)) interval(1m,5m)", expectErrInfo=f"Invalid function para type: sum(c6)", fullMatched=False)

    def s3_show_rsma(self):
        tdSql.query("show rsmas")
        tdSql.checkRows(2)
        tdSql.query("show d0.rsmas")
        tdSql.checkRows(2)
        tdSql.query("show d1.rsmas")
        tdSql.checkRows(0)
        tdSql.query("select * from information_schema.ins_rsmas")
        tdSql.checkRows(2)
        tdSql.query("select * from information_schema.ins_rsmas where db_name='d0'")
        tdSql.checkRows(2)
        tdSql.error("show create rsma d0.rsma_not_exist", expectErrInfo=f"Rsma not exist", fullMatched=False)
        tdSql.query("show create rsma d0.rsma1")
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '`rsma1`')
        tdSql.checkData(0, 1, 'CREATE RSMA `rsma1` ON `d0`.`stb0` FUNCTION(min(`c0`),max(`c1`),avg(`c2`),sum(`c3`),first(`c4`),last(`c5`)) INTERVAL(60000a,300000a)')
        tdSql.execute("drop rsma d0.rsma1")
        tdSql.query("show rsmas")
        tdSql.checkRows(1)
        tdSql.execute('CREATE RSMA `rsma1` ON `d0`.`stb0` FUNCTION(min(`c0`),max(`c1`),avg(`c2`),sum(`c3`),first(`c4`),last(`c5`)) INTERVAL(60000a,300000a)')
        tdSql.query("show rsmas")
        tdSql.checkRows(2)
        tdSql.query("show create rsma d0.rsma7")
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '`rsma7`')
        tdSql.checkData(0, 1, 'CREATE RSMA `rsma7` ON `d0`.`stb1` FUNCTION(min(`c0`),max(`c1`),avg(`c2`),sum(`c3`),first(`c4`),last(`c5`),first(`c6`)) INTERVAL(60000a,300000a)')
        tdSql.execute("drop rsma `d0`.`rsma7`")
        tdSql.query("show rsmas")
        tdSql.checkRows(1)
        tdSql.execute('CREATE RSMA `rsma7` ON `d0`.`stb1` FUNCTION(min(`c0`),max(`c1`),avg(`c2`),sum(`c3`),first(`c4`),last(`c5`),first(`c6`)) INTERVAL(60000a,300000a)')
        tdSql.query("show rsmas")
        tdSql.checkRows(2)

    def s4_drop_rsma(self):
        tdSql.query("show rsmas")
        tdSql.checkRows(2)
        tdSql.execute("drop rsma rsma1")
        tdSql.query("show rsmas")
        tdSql.checkRows(1)
        tdSql.execute("drop stable stb1")
        tdSql.query("show rsmas")
        tdSql.checkRows(0)
        self.s0_reset_test_env()
        tdSql.query("show rsmas")
        tdSql.checkRows(2)
        tdSql.execute("drop database d0")
        tdSql.query("show rsmas")
        tdSql.checkRows(0)

    def s5_0_check_rollup_result(self):
        tdSql.query("select * from d0.stb0")
        tdSql.checkRows(6)
        tdSql.query("select * from d0.ctb0 order by ts")
        tdSql.checkRows(3)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 1)
        tdSql.checkData(0, 2, 3)
        tdSql.checkData(0, 3, 2)
        tdSql.checkData(0, 4, 6)
        tdSql.checkData(0, 5, True)
        tdSql.checkData(0, 6, 3)
        tdSql.checkData(0, 7, 3)
        tdSql.checkData(1, 0, '2024-10-01 08:01:00.000')
        tdSql.checkData(1, 1, 4)
        tdSql.checkData(1, 2, 5)
        tdSql.checkData(1, 3, 5)
        tdSql.checkData(1, 4, 11)
        tdSql.checkData(1, 5, False)
        tdSql.checkData(1, 6, 4)
        tdSql.checkData(1, 7, 444)
        tdSql.checkData(2, 0, '2024-10-01 08:02:00.000')
        tdSql.checkData(2, 1, 5)
        tdSql.checkData(2, 2, 5)
        tdSql.checkData(2, 3, 5)
        tdSql.checkData(2, 4, 5)
        tdSql.checkData(2, 5, True)
        tdSql.checkData(2, 6, 5)
        tdSql.checkData(2, 7, 5)
        tdSql.query("select * from d0.ctb1 order by ts")
        tdSql.checkRows(3)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 22)
        tdSql.checkData(0, 2, 333)
        tdSql.checkData(0, 3, 2)
        tdSql.checkData(0, 4, 6)
        tdSql.checkData(0, 5, True)
        tdSql.checkData(0, 6, 3)
        tdSql.checkData(0, 7, 33)
        tdSql.checkData(1, 0, '2024-10-01 08:01:00.000')
        tdSql.checkData(1, 1, 14)
        tdSql.checkData(1, 2, 4)
        tdSql.checkData(1, 3, 4)
        tdSql.checkData(1, 4, 4)
        tdSql.checkData(1, 5, False)
        tdSql.checkData(1, 6, 40)
        tdSql.checkData(1, 7, 4)
        tdSql.checkData(2, 0, '2024-10-01 08:02:00.000')
        tdSql.checkData(2, 1, 5)
        tdSql.checkData(2, 2, 555)
        tdSql.checkData(2, 3, 5)
        tdSql.checkData(2, 4, 5)
        tdSql.checkData(2, 5, True)
        tdSql.checkData(2, 6, 5)
        tdSql.checkData(2, 7, 5)
        tdSql.query("select * from d0.stb1 order by ts")
        tdSql.checkRows(3)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 33)
        tdSql.checkData(0, 2, 22)
        tdSql.checkData(0, 3, 333)
        tdSql.checkData(0, 4, 2)
        tdSql.checkData(0, 5, 6)
        tdSql.checkData(0, 6, True)
        tdSql.checkData(0, 7, 3)
        tdSql.checkData(0, 8, 1)
        tdSql.checkData(1, 0, '2024-10-01 08:01:00.000')
        tdSql.checkData(1, 1, 14)
        tdSql.checkData(1, 2, 14)
        tdSql.checkData(1, 3, 4)
        tdSql.checkData(1, 4, 4)
        tdSql.checkData(1, 5, 4)
        tdSql.checkData(1, 6, False)
        tdSql.checkData(1, 7, 40)
        tdSql.checkData(1, 8, 4)
        tdSql.checkData(2, 0, '2024-10-01 08:02:00.000')
        tdSql.checkData(2, 1, 55)
        tdSql.checkData(2, 2, 5)
        tdSql.checkData(2, 3, 555)
        tdSql.checkData(2, 4, 5)
        tdSql.checkData(2, 5, 5)
        tdSql.checkData(2, 6, True)
        tdSql.checkData(2, 7, 5)
        tdSql.checkData(2, 8, 5)

    def s5_0_wait_trim_done(self):
        i = 0
        while True:
            tdSql.query("show retentions")
            if tdSql.getRows() == 0:
                break
            time.sleep(1)
            i += 1
            tdLog.info(f"wait for trim/rollup done, {i} second(s) elapsed")

    def s5_0_trim_db(self, tsdbOpType = 'trim'):
        self.s0_reset_test_env()
        tdSql.query("show rsmas")
        tdSql.checkRows(2)
        tdSql.query("select * from d0.stb0")
        tdSql.checkRows(11)
        tdSql.query("select * from d0.stb1")
        tdSql.checkRows(5)
        tdSql.execute("alter database d0 keep 30d,36500d")
        tdSql.execute("flush database d0")
        tdSql.execute(f"{tsdbOpType} database d0")
        tdSql.query("show retentions")
        tdSql.checkRows(1)
        tdSql.query(f"show retention {tdSql.queryResult[0][0]}")
        tdSql.error("trim database d0", expectErrInfo=f"Trim or rollup already exist", fullMatched=False)
        tdSql.error("rollup database d0", expectErrInfo=f"Trim or rollup already exist", fullMatched=False)
        self.s5_0_wait_trim_done()
        self.s5_0_check_rollup_result()

    def s5_trim_db(self):
        tdLog.info("trim database")
        self.s5_0_trim_db('trim')
        # insert more data after trim
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:00:01.001',1,1,1,1,true, '1','1')")
        tdSql.query("select * from d0.stb0")
        tdSql.checkRows(7)
        tdSql.execute("flush database d0")
        # trim again
        time.sleep(5) # ensure commit is done
        tdSql.execute("trim database d0")
        tdSql.query("show retentions")
        tdSql.checkRows(1)
        self.s5_0_wait_trim_done()
        # trim has no effect since no fset retention happen 
        tdSql.query("select * from d0.stb0")
        tdSql.checkRows(7)
        # 2nd level rollup has effect since expLevel changed
        tdLog.info("2nd level rollup by trim")
        tdSql.execute("alter database d0 keep 30d,60d,36500d")
        tdSql.execute("trim database d0")
        tdSql.query("show retentions")
        tdSql.checkRows(1)
        self.s5_0_wait_trim_done()
        tdSql.query("select * from d0.stb0")
        tdSql.checkRows(2)
        tdSql.query("select * from d0.stb1")
        tdSql.checkRows(1)
        tdSql.query("select * from d0.ctb0")
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 1)
        tdSql.checkData(0, 2, 5)
        tdSql.checkData(0, 3, 3.25)
        tdSql.checkData(0, 4, 23)
        tdSql.checkData(0, 5, True)
        tdSql.checkData(0, 6, 5)
        tdSql.checkData(0, 7, 5)
        tdSql.query("select * from d0.ctb1")
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 5)
        tdSql.checkData(0, 2, 555)
        tdSql.checkData(0, 3, 3.66667)
        tdSql.checkData(0, 4, 15)
        tdSql.checkData(0, 5, True)
        tdSql.checkData(0, 6, 5)
        tdSql.checkData(0, 7, 5)
        tdSql.query("select * from d0.ctb11")
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 55)
        tdSql.checkData(0, 2, 5)
        tdSql.checkData(0, 3, 555)
        tdSql.checkData(0, 4, 3.66667)
        tdSql.checkData(0, 5, 15)
        tdSql.checkData(0, 6, True)
        tdSql.checkData(0, 7, 5)
        tdSql.checkData(0, 8, 1)


    def s6_rollup_db(self):
        tdLog.info("rollup database")
        self.s5_0_trim_db('rollup')
        # insert more data after rollup
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:00:01.001',1,1,1,1,true, '1','1')")
        tdSql.query("select * from d0.stb0")
        tdSql.checkRows(7)
        tdSql.execute("flush database d0")
        # rollup again
        time.sleep(5) # ensure commit is done
        tdSql.execute("rollup database d0")
        tdSql.query("show retentions")
        tdSql.checkRows(1)
        self.s5_0_wait_trim_done()
        # rollup has effect since new commit happen after last rollup
        tdSql.query("select * from d0.stb0")
        tdSql.checkRows(6)
        tdSql.query("select * from d0.ctb0")
        tdSql.checkRows(3)
        # check rollup result
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 1)
        tdSql.checkData(0, 2, 3)
        tdSql.checkData(0, 3, 1.5)
        tdSql.checkData(0, 4, 7)
        tdSql.checkData(0, 5, True)
        tdSql.checkData(0, 6, 1)
        tdSql.checkData(0, 7, 1)
        # 2nd level rollup has effect since expLevel changed
        tdLog.info("2nd level rollup")
        tdSql.execute("alter database d0 keep 30d,60d,36500d")
        tdSql.execute("rollup database d0")
        tdSql.query("show retentions")
        tdSql.checkRows(1)
        self.s5_0_wait_trim_done()
        tdSql.query("select * from d0.stb0")
        tdSql.checkRows(2)
        tdSql.query("select * from d0.stb1")
        tdSql.checkRows(1)
        tdSql.query("select * from d0.ctb0")
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 1)
        tdSql.checkData(0, 2, 5)
        tdSql.checkData(0, 3, 3.83333)
        tdSql.checkData(0, 4, 23)
        tdSql.checkData(0, 5, True)
        tdSql.checkData(0, 6, 5)
        tdSql.checkData(0, 7, 5)
        tdSql.query("select * from d0.ctb1")
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 5)
        tdSql.checkData(0, 2, 555)
        tdSql.checkData(0, 3, 3.66667)
        tdSql.checkData(0, 4, 15)
        tdSql.checkData(0, 5, True)
        tdSql.checkData(0, 6, 5)
        tdSql.checkData(0, 7, 5)
        tdSql.query("select * from d0.ctb11")
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 55)
        tdSql.checkData(0, 2, 5)
        tdSql.checkData(0, 3, 555)
        tdSql.checkData(0, 4, 3.66667)
        tdSql.checkData(0, 5, 15)
        tdSql.checkData(0, 6, True)
        tdSql.checkData(0, 7, 5)
        tdSql.checkData(0, 8, 1)

    def s7_rollup_vgroups(self):
        tdLog.info("rollup vgroups")
        self.s5_0_trim_db('rollup')
        # insert more data after rollup
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:00:01.001',1,1,1,1,true, '1','1')")
        tdSql.query("select * from d0.stb0")
        tdSql.checkRows(7)
        tdSql.execute("flush database d0")
        # rollup again by vgroups
        time.sleep(5) # ensure commit is done00) 
        tdSql.error("rollup d0.vgroups in (1000)", expectErrInfo=f"Vgroup does not exist or not in db", fullMatched=False)
        tdSql.error("rollup vgroups in (2000,3000)", expectErrInfo=f"Vgroup does not exist or not in db", fullMatched=False)
        tdSql.error("rollup vgroups in (2000,3000) start with '2025-12-30 10:00:00.000'", expectErrInfo=f"Vgroup does not exist or not in db", fullMatched=False)
        tdSql.query("show d0.vgroups")
        tdSql.checkRows(2)
        tdSql.execute(f"rollup d0.vgroups in (%d,%d) start with '2025-12-30 10:00:00.000'" % (tdSql.queryResult[0][0], tdSql.queryResult[1][0]))
        tdSql.query("show retentions")
        tdSql.checkRows(1)
        self.s5_0_wait_trim_done()
        # rollup has no effect since the start time is later than any data
        tdSql.query("select * from d0.stb0")
        tdSql.checkRows(7)
        tdSql.query("show d0.vgroups")
        tdSql.checkRows(2)
        tdSql.execute(f"rollup vgroups in (%d,%d) start with '2024-10-01 08:00:00.000'" % (tdSql.queryResult[0][0], tdSql.queryResult[1][0]))
        tdSql.query("show retentions")
        tdSql.checkRows(1)
        self.s5_0_wait_trim_done()
        # rollup has effect since new commit happen after last rollup
        tdSql.query("select * from d0.stb0")
        tdSql.checkRows(6)
        tdSql.query("select * from d0.ctb0")
        tdSql.checkRows(3)
        # check rollup result
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 1)
        tdSql.checkData(0, 2, 3)
        tdSql.checkData(0, 3, 1.5)
        tdSql.checkData(0, 4, 7)
        tdSql.checkData(0, 5, True)
        tdSql.checkData(0, 6, 1)
        tdSql.checkData(0, 7, 1)
        # 2nd level rollup has effect since expLevel changed
        tdLog.info("2nd level rollup vgroups")
        tdSql.execute("alter database d0 keep 30d,60d,36500d")
        tdSql.query("show d0.vgroups")
        tdSql.checkRows(2)
        tdSql.execute(f"rollup vgroups in (%d,%d)" % (tdSql.queryResult[0][0], tdSql.queryResult[1][0]))
        tdSql.query("show retentions")
        tdSql.checkRows(1)
        self.s5_0_wait_trim_done()
        tdSql.query("select * from d0.stb0")
        tdSql.checkRows(2)
        tdSql.query("select * from d0.stb1")
        tdSql.checkRows(1)
        tdSql.query("select * from d0.ctb0")
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 1)
        tdSql.checkData(0, 2, 5)
        tdSql.checkData(0, 3, 3.83333)
        tdSql.checkData(0, 4, 23)
        tdSql.checkData(0, 5, True)
        tdSql.checkData(0, 6, 5)
        tdSql.checkData(0, 7, 5)
        tdSql.query("select * from d0.ctb1")
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 5)
        tdSql.checkData(0, 2, 555)
        tdSql.checkData(0, 3, 3.66667)
        tdSql.checkData(0, 4, 15)
        tdSql.checkData(0, 5, True)
        tdSql.checkData(0, 6, 5)
        tdSql.checkData(0, 7, 5)
        tdSql.query("select * from d0.ctb11")
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 55)
        tdSql.checkData(0, 2, 5)
        tdSql.checkData(0, 3, 555)
        tdSql.checkData(0, 4, 3.66667)
        tdSql.checkData(0, 5, 15)
        tdSql.checkData(0, 6, True)
        tdSql.checkData(0, 7, 5)
        tdSql.checkData(0, 8, 1)

    def s8_decimal_composite_key_add_drop_column(self):
        tdLog.info("decimal and composite key")
        tdSql.execute("alter all dnodes 'queryTrimIntervalSec 1'")
        tdSql.execute("drop database if exists d0")
        tdSql.execute("create database if not exists d0 replica 1 keep 36500d stt_trigger 1")
        tdSql.execute("drop database if exists d1")
        tdSql.execute("create database if not exists d1 replica 1 keep 36500d stt_trigger 1")
        tdSql.execute("use d0")
        tdSql.execute("create stable if not exists stb0 (ts timestamp, c0 int composite key, c1 decimal(10,2), c2 decimal(30,5), c3 geometry(100), c4 double) tags(t0 int)")
        tdSql.execute("create stable if not exists stb1 (ts timestamp, c0 varchar(10) composite key, c1 decimal(10,2), c2 geometry(100)) tags(t0 int)")
        tdSql.execute("create table if not exists ctb0 using stb0 tags(0)")
        tdSql.execute("create table if not exists ctb1 using stb0 tags(1)")
        tdSql.execute("create table if not exists ctb11 using stb1 tags(11)")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:00:01.001',1,999.99,9999999999.9999,'LINESTRING (1.000000 1.000000, 2.000000 2.000000)',1.0)")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:00:01.001',2,888.8,888888888.888,'LINESTRING (1.000000 1.000000, 2.000000 3.000000)',2.0)")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:00:03.003',3,777.7,777777777.77,'LINESTRING (1.000000 1.000000, 2.000000 4.000000)',3.0)")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:01:00.001',4,555.55,5555555555.55555,'LINESTRING (1.000000 1.000000, 2.000000 5.000000)',7.0)")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:01:00.001',6,666.66,6666666666.66666,'LINESTRING (1.000000 1.000000, 2.000000 6.000000)',4.0)")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:02:00.002',5,444.44,4444444444.44444,'LINESTRING (1.000000 1.000000, 2.000000 7.000000)',5.0)")
        tdSql.execute("insert  into ctb1 values('2024-10-01 08:00:01.001',111,999.99,9999999999.9999,'LINESTRING (1.000000 1.000000, 2.000000 8.000000)',1.0)")
        tdSql.execute("insert  into ctb1 values('2024-10-01 08:00:02.002',22,888.8,888888888.888,'LINESTRING (1.000000 1.000000, 2.000000 9.000000)',2.0)")
        tdSql.execute("insert  into ctb1 values('2024-10-01 08:00:03.003',33,777.7,777777777.77,'LINESTRING (1.000000 1.000000, 2.000000 10.000000)',3.0)")
        tdSql.execute("insert  into ctb1 values('2024-10-01 08:01:00.001',14,666.66,6666666666.66666,'LINESTRING (1.000000 1.000000, 2.000000 11.000000)',4.0)")
        tdSql.execute("insert  into ctb1 values('2024-10-01 08:02:00.002',5,555,5,'LINESTRING (1.000000 1.000000, 2.000000 12.000000)',5.0)")
        tdSql.execute("insert  into ctb11 values('2024-10-01 08:00:01.001','111',111,'LINESTRING (1.000000 1.000000, 2.000000 100.000000)')")
        tdSql.execute("insert  into ctb11 values('2024-10-01 08:00:02.002','22',22,'LINESTRING (1.000000 1.000000, 2.000000 101.000000)')")
        tdSql.execute("insert  into ctb11 values('2024-10-01 08:00:02.002','33',333,'LINESTRING (1.000000 1.000000, 2.000000 102.000000)')")
        tdSql.execute("insert  into ctb11 values('2024-10-01 08:01:00.001','14',4,'LINESTRING (1.000000 1.000000, 2.000000 103.000000)')")
        tdSql.execute("insert  into ctb11 values('2024-10-01 08:02:00.002','55',555,'LINESTRING (1.000000 1.000000, 2.000000 104.000000)')")
        tdSql.execute("flush database d0")
        tdSql.query("select * from stb0")
        tdSql.checkRows(11)
        tdSql.query("select * from stb1")
        tdSql.checkRows(5)
        tdSql.query("show rsmas")
        tdSql.checkRows(0)
        # create rsma
        tdSql.execute("create rsma rsma1 on d0.stb0 function(first(c0), min(c1), max(c2), first(c3)) interval(1m,5m)")
        tdSql.execute("alter rsma rsma1 function(sum(c4))")

        tdSql.error("alter rsma rsma1 function(min(c0), max(c1), avg(c2), sum(c3),first(c4),last(c5))", expectErrInfo=f"Invalid func count for rsma, should be in range [1, 5]", fullMatched=False)
        tdSql.error("alter rsma rsma1 function(min(c0), max(c1), avg(c2), sum(c3),first(c4))", expectErrInfo=f"Invalid func param for rsma since composite key column can only be first/last: min(c0)", fullMatched=False)
        tdSql.error("alter rsma rsma1 function(last(c0)) interval(1m,5m)", expectErrInfo=f"syntax error near", fullMatched=False)
        tdSql.error("alter rsma rsma1_ function(last(c0))", expectErrInfo=f"Rsma not exist", fullMatched=False)
        tdSql.error("alter rsma rsma1 function(last(c0))", expectErrInfo=f"Rsma func already specified for column: c0", fullMatched=False)
        tdSql.error("create rsma rsma1 on d0.stb0 function(avg(c3)) interval(1m,5m)", expectErrInfo=f"Invalid function para type: avg(c3)", fullMatched=False)
        tdSql.error("create rsma rsma1 on d0.stb0 function(sum(c3)) interval(1m,5m)", expectErrInfo=f"Invalid function para type: sum(c3)", fullMatched=False)
        tdSql.error("create rsma rsma1 on d0.stb0 function(min(c3)) interval(1m,5m)", expectErrInfo=f"Invalid function para type: min(c3)", fullMatched=False)
        tdSql.error("create rsma rsma1 on d0.stb0 function(max(c3),max(c2)) interval(1m,5m)", expectErrInfo=f"Invalid function para type: max(c3)", fullMatched=False)

        tdSql.execute("create rsma rsma2 on d0.stb1 function(last(c0), min(c1)) interval(1m,5m)")
        tdSql.execute("alter rsma rsma2 function(last(c2))")
        tdSql.query("show d0.rsmas")
        tdSql.checkRows(2)
        tdSql.query("select * from information_schema.ins_rsmas where db_name='d0'")
        tdSql.checkRows(2)
        # rollup rsma and check the result
        tdSql.execute("alter database d0 keep 30d,36500d")
        tdSql.execute("rollup database d0")
        self.s5_0_wait_trim_done()
        tdSql.query("select * from d0.stb0")
        tdSql.checkRows(6)
        tdSql.query("select * from d0.stb1")
        tdSql.checkRows(3)
        tdSql.query("select ts,c0,c1,c2,ST_AsText(c3),c4 from d0.ctb0")
        tdSql.checkRows(3)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 1)
        tdSql.checkData(0, 2, Decimal('777.70'))
        tdSql.checkData(0, 3, Decimal('9999999999.99990'))
        tdSql.checkData(0, 4, 'LINESTRING (1.000000 1.000000, 2.000000 2.000000)')
        tdSql.checkData(0, 5, 6)
        tdSql.checkData(1, 0, '2024-10-01 08:01:00.000')
        tdSql.checkData(1, 1, 4)
        tdSql.checkData(1, 2, Decimal('555.55'))
        tdSql.checkData(1, 3, Decimal('6666666666.66666'))
        tdSql.checkData(1, 4, 'LINESTRING (1.000000 1.000000, 2.000000 5.000000)')
        tdSql.checkData(1, 5, 11)
        tdSql.checkData(2, 0, '2024-10-01 08:02:00.000')
        tdSql.checkData(2, 1, 5)
        tdSql.checkData(2, 2, Decimal('444.44'))
        tdSql.checkData(2, 3, Decimal('4444444444.44444'))
        tdSql.checkData(2, 4, 'LINESTRING (1.000000 1.000000, 2.000000 7.000000)')
        tdSql.checkData(2, 5, 5)
        tdSql.query("select ts,c0,c1,c2,ST_AsText(c3),c4 from d0.ctb1")
        tdSql.checkRows(3)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 111)
        tdSql.checkData(0, 2, Decimal('777.70'))
        tdSql.checkData(0, 3, Decimal('9999999999.99990'))
        tdSql.checkData(0, 4, 'LINESTRING (1.000000 1.000000, 2.000000 8.000000)')
        tdSql.checkData(0, 5, 6)
        tdSql.checkData(1, 0, '2024-10-01 08:01:00.000')
        tdSql.checkData(1, 1, 14)
        tdSql.checkData(1, 2, Decimal('666.66'))
        tdSql.checkData(1, 3, Decimal('6666666666.66666'))
        tdSql.checkData(1, 4, 'LINESTRING (1.000000 1.000000, 2.000000 11.000000)')
        tdSql.checkData(1, 5, 4)
        tdSql.checkData(2, 0, '2024-10-01 08:02:00.000')
        tdSql.checkData(2, 1, 5)
        tdSql.checkData(2, 2, Decimal('555.00'))
        tdSql.checkData(2, 3, Decimal('5.00000'))
        tdSql.checkData(2, 4, 'LINESTRING (1.000000 1.000000, 2.000000 12.000000)')
        tdSql.checkData(2, 5, 5)
        tdSql.query("select ts,c0,c1,ST_AsText(c2) from d0.ctb11")
        tdSql.checkRows(3)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 33)
        tdSql.checkData(0, 2, Decimal('22.00'))
        tdSql.checkData(0, 3, 'LINESTRING (1.000000 1.000000, 2.000000 102.000000)')
        tdSql.checkData(1, 0, '2024-10-01 08:01:00.000')
        tdSql.checkData(1, 1, 14)
        tdSql.checkData(1, 2, Decimal('4.00'))
        tdSql.checkData(1, 3, 'LINESTRING (1.000000 1.000000, 2.000000 103.000000)')
        tdSql.checkData(2, 0, '2024-10-01 08:02:00.000')
        tdSql.checkData(2, 1, 55)
        tdSql.checkData(2, 2, Decimal('555.00'))
        tdSql.checkData(2, 3, 'LINESTRING (1.000000 1.000000, 2.000000 104.000000)')
        # rollup again to 2nd level
        tdSql.execute("alter database d0 keep 30d,60d,36500d")
        tdSql.execute("rollup database d0")
        self.s5_0_wait_trim_done()
        tdSql.query("select * from d0.stb0")
        tdSql.checkRows(2)
        tdSql.query("select * from d0.stb1")
        tdSql.checkRows(1)
        tdSql.query("select ts,c0,c1,c2,ST_AsText(c3),c4 from d0.ctb0")
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 1)
        tdSql.checkData(0, 2, Decimal('444.44'))
        tdSql.checkData(0, 3, Decimal('9999999999.99990'))
        tdSql.checkData(0, 4, 'LINESTRING (1.000000 1.000000, 2.000000 2.000000)')
        tdSql.checkData(0, 5, 22)
        tdSql.query("select ts,c0,c1,c2,ST_AsText(c3),c4 from d0.ctb1")
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 111)
        tdSql.checkData(0, 2, Decimal('555.00'))
        tdSql.checkData(0, 3, Decimal('9999999999.99990'))
        tdSql.checkData(0, 4, 'LINESTRING (1.000000 1.000000, 2.000000 8.000000)')
        tdSql.checkData(0, 5, 15)
        tdSql.query("select ts,c0,c1,ST_AsText(c2) from d0.ctb11")
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 55)
        tdSql.checkData(0, 2, Decimal('4.00'))
        tdSql.checkData(0, 3, 'LINESTRING (1.000000 1.000000, 2.000000 104.000000)')
        # add column c5 to stb0 and check rsma still works
        tdSql.execute("alter table stb0 add column c5 bigint")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:00:00.001',2,111.11,1111111111.11111,'LINESTRING (1.000000 1.000000, 2.000000 20.000000)',10.0,100)")
        tdSql.execute("flush database d0")
        time.sleep(3) # ensure commit is done
        tdSql.execute("rollup database d0")
        self.s5_0_wait_trim_done()
        tdSql.query("select * from d0.stb0")
        tdSql.checkRows(2)
        tdSql.query("select ts,c0,c1,c2,ST_AsText(c3),c4,c5 from d0.ctb0")
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 1)
        tdSql.checkData(0, 2, Decimal('111.11'))
        tdSql.checkData(0, 3, Decimal('9999999999.99990'))
        tdSql.checkData(0, 4, 'LINESTRING (1.000000 1.000000, 2.000000 2.000000)')
        tdSql.checkData(0, 5, 32)
        tdSql.checkData(0, 6, 100)
        # drop column c3 from stb0 and check rsma still works
        tdSql.execute("alter table stb0 drop column c3")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:00:00.001',2,111.22,1111.11111,20.0,50)")
        tdSql.execute("flush database d0")
        time.sleep(3) # ensure commit is done
        tdSql.execute("rollup database d0")
        self.s5_0_wait_trim_done()
        tdSql.query("select * from d0.stb0")
        tdSql.checkRows(2)
        tdSql.query("select ts,c0,c1,c2,c4,c5 from d0.ctb0")
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 1)
        tdSql.checkData(0, 2, Decimal('111.11'))
        tdSql.checkData(0, 3, Decimal('9999999999.99990'))
        tdSql.checkData(0, 4, 52)
        tdSql.checkData(0, 5, 50)
        tdSql.query("select ts,c0,c1,c2,c4,c5 from d0.ctb1")
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 111)
        tdSql.checkData(0, 2, Decimal('555.00'))
        tdSql.checkData(0, 3, Decimal('9999999999.99990'))
        tdSql.checkData(0, 4, 15)
        tdSql.checkData(0, 5, None)

        # drop column c2 from stb1 and check rsma still works
        tdSql.execute("alter table stb1 drop column c2")
        tdSql.execute("insert  into ctb11 values('2024-10-01 08:00:00.001', '33', 5)")
        tdSql.execute("flush database d0")
        time.sleep(3) # ensure commit is done
        tdSql.execute("rollup database d0")
        self.s5_0_wait_trim_done()
        tdSql.query("select * from d0.stb1")
        tdSql.checkRows(1)
        tdSql.query("select * from d0.ctb11")
        tdSql.checkRows(1)
        tdSql.checkData(0, 0, '2024-10-01 08:00:00.000')
        tdSql.checkData(0, 1, 33)
        tdSql.checkData(0, 2, Decimal('4.00'))

    def s9_negative_ts(self):
        tdLog.info("check negative timestamp")
        tdSql.execute("alter all dnodes 'queryTrimIntervalSec 1'")
        tdSql.execute("drop database if exists d0")
        tdSql.execute("create database if not exists d0 replica 1 keep 73000d stt_trigger 1")
        tdSql.execute("use d0")
        tdSql.execute("create stable if not exists stb0 (ts timestamp, c0 int composite key, c1 decimal(10,2), c2 decimal(30,5), c3 geometry(100), c4 double) tags(t0 int)")
        tdSql.execute("create table if not exists ctb0 using stb0 tags(0)")
        tdSql.execute("insert  into ctb0 values('1969-10-01 08:00:01.001',1,999.99,9999999999.9999,'LINESTRING (1.000000 1.000000, 2.000000 2.000000)',1.0)")
        tdSql.execute("insert  into ctb0 values('1969-10-01 08:00:01.001',2,888.8,888888888.888,'LINESTRING (1.000000 1.000000, 2.000000 3.000000)',2.0)")
        tdSql.execute("insert  into ctb0 values('1969-10-01 08:00:03.003',3,777.7,777777777.77,'LINESTRING (1.000000 1.000000, 2.000000 4.000000)',3.0)")
        tdSql.execute("insert  into ctb0 values('1969-10-01 08:01:00.001',4,555.55,5555555555.55555,'LINESTRING (1.000000 1.000000, 2.000000 5.000000)',7.0)")
        tdSql.execute("insert  into ctb0 values('1969-10-01 08:01:00.001',6,666.66,6666666666.66666,'LINESTRING (1.000000 1.000000, 2.000000 6.000000)',4.0)")
        tdSql.execute("insert  into ctb0 values('1969-10-01 08:02:00.002',5,444.44,4444444444.44444,'LINESTRING (1.000000 1.000000, 2.000000 7.000000)',5.0)")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:00:01.001',1,999.99,9999999999.9999,'LINESTRING (1.000000 1.000000, 2.000000 2.000000)',1.0)")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:00:01.001',2,888.8,888888888.888,'LINESTRING (1.000000 1.000000, 2.000000 3.000000)',2.0)")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:00:03.003',3,777.7,777777777.77,'LINESTRING (1.000000 1.000000, 2.000000 4.000000)',3.0)")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:01:00.001',4,555.55,5555555555.55555,'LINESTRING (1.000000 1.000000, 2.000000 5.000000)',7.0)")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:01:00.001',6,666.66,6666666666.66666,'LINESTRING (1.000000 1.000000, 2.000000 6.000000)',4.0)")
        tdSql.execute("insert  into ctb0 values('2024-10-01 08:02:00.002',5,444.44,4444444444.44444,'LINESTRING (1.000000 1.000000, 2.000000 7.000000)',5.0)")
        tdSql.execute("flush database d0")
        tdSql.query("select * from stb0")
        tdSql.checkRows(12)
        tdSql.query("show rsmas")
        tdSql.checkRows(0)
        # create rsma
        tdSql.execute("create rsma rsma1 on d0.stb0 function(first(c0), min(c1), max(c2), first(c3)) interval(1m,5m)")
        tdSql.execute("alter rsma rsma1 function(sum(c4))")
        tdSql.query("select * from information_schema.ins_rsmas where db_name='d0'")
        tdSql.checkRows(1)
        # rollup rsma and check the result
        tdSql.execute("alter database d0 keep 30d,73000d")
        tdSql.execute("rollup database d0")
        self.s5_0_wait_trim_done()
        tdSql.query("select * from d0.stb0")
        tdSql.checkRows(6)
        tdSql.query("select ts,c0,c1,c2,ST_AsText(c3),c4 from d0.ctb0")
        tdSql.checkRows(6)
        expected_data = [
            ('08:00:00.000', 1, Decimal('777.70'), Decimal('9999999999.99990'), 'LINESTRING (1.000000 1.000000, 2.000000 2.000000)', 6),
            ('08:01:00.000', 4, Decimal('555.55'), Decimal('6666666666.66666'), 'LINESTRING (1.000000 1.000000, 2.000000 5.000000)', 11),
            ('08:02:00.000', 5, Decimal('444.44'), Decimal('4444444444.44444'), 'LINESTRING (1.000000 1.000000, 2.000000 7.000000)', 5),
        ]
        row_offset = 0
        for date_prefix in ["1969-10-01", "2024-10-01"]:
            for i, (time, c0, c1, c2, c3, c4) in enumerate(expected_data):
                row = i + row_offset
                tdSql.checkData(row, 0, f"{date_prefix} {time}")
                tdSql.checkData(row, 1, c0)
                tdSql.checkData(row, 2, c1)
                tdSql.checkData(row, 3, c2)
                tdSql.checkData(row, 4, c3)
                tdSql.checkData(row, 5, c4)
            row_offset += len(expected_data)

    def test_rsma(self):
        """RSMAs basic
        
        1. Create two databases d0 and d1 with retention policy and stt_trigger.
        2. Create stable and table with various data types including decimal and composite key.
        3. Insert data into tables.
        4. Create rsma on tables with various functions.
        5. Alter rsma to add more functions.
        6. Show rsma and verify.
        7. Drop rsma and verify.
        8. Trim database to trigger retention policy.
        9. Rollup database and verify rsma results.
        10. Rollup vgroups and verify rsma results.
        11. Test decimal data type and composite key with add/drop column operations.
        12. Exceptional cases.
        13. Retention task monitor.
        14. Rollup automatically when execute: trim database.
        15. Rollup manually when execute: rollup database.

        Catalog:
            - Rollup SMA:Create/Drop/Show/Query/Trim/Rollup

        Since: v3.3.8.0

        Lables: common,ci,rsma

        Jira: TS-6113

        History:
            - 2025-09-25: Initial version from Kaili Xu(TS-6113).
            - 2025-11-04: Check negative ts from Kaili Xu(TD-38485).
        """
        self.s1_create_db_table()
        self.s2_create_rsma()
        self.s3_show_rsma()
        self.s4_drop_rsma()
        self.s5_trim_db()
        self.s6_rollup_db()
        self.s7_rollup_vgroups()
        self.s8_decimal_composite_key_add_drop_column()
        self.s9_negative_ts()

        tdLog.success("%s successfully executed" % __file__)
